package com.xxx.dorm.dao.impl;

import com.xxx.dorm.dao.StudentDao;
import com.xxx.dorm.domain.Student;
import com.xxx.dorm.utils.JDBCUtils;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;

import java.util.List;

public class StudentDaoImpl implements StudentDao {

    private JdbcTemplate template = new JdbcTemplate(JDBCUtils.getDataSource());

    @Override
    public List<Student> findAll() {
        String sql;
        sql = "select * from t_student ORDER BY stuCode";
        return template.query(sql,new BeanPropertyRowMapper<>(Student.class));
    }

    @Override
    public void addStudent(Student student) {
        String sql = "insert into t_student(stuCode,name,sex,phone,grade,did) values(?,?,?,?,?,?)";
        template.update(sql,student.getStuCode(),student.getName(),student.getSex(),student.getPhone(),student.getGrade(),student.getDid());
    }

    @Override
    public void delete(Integer uid) {
        String sql = "update tb_user set disabled = ? where id = ?";
        template.update(sql,1,uid);
    }

    @Override
    public Student findById(Integer id) {
        String sql = "select * from t_student where id = ?";
        return template.queryForObject(sql,new BeanPropertyRowMapper<>(Student.class),id);
    }

    @Override
    public void updateStudent(Student student) {
        String sql = "update t_student set name = ?,sex = ?,phone = ?,grade = ?,did = ? where id = ?";
        template.update(sql,student.getName(),student.getSex(),student.getPhone(),student.getGrade(),student.getDid(),student.getId());
    }

    /**
     * 删除学生
     * @param id
     */
    @Override
    public void removeStudent(String id) {
        String sql = "delete from t_student where id = ?";
        template.update(sql,id);
    }

    /**
     * 带条件查找
     * @param conditions
     * @param information
     * @return
     */
    @Override
    public List<Student> find(String conditions, String information) {
        String sql = null;
        if(conditions.equals("stuCode")){
            sql = "select * from t_student where stuCode = ?";
        }else if(conditions.equals("name")){
            sql = "select * from t_student where name = ?";
        }else if(conditions.equals("grade")){
            sql = "select * from t_student where grade = ?";
        }else if(conditions.equals("buildingname_dormname")){
            sql = "SELECT t_student.id,t_student.stuCode,t_student.name,t_student.sex,t_student.phone,t_student.grade,t_student.did " +
                    "from t_student,t_dormitory,t_building WHERE t_student.did = t_dormitory.id and t_building.name = ? and t_dormitory.number = ? and t_dormitory.bid = t_building.id";
            return template.query(sql,new BeanPropertyRowMapper<>(Student.class),information.split("#")[0]+"",information.split("#")[1]+"");
        }else if(conditions.equals("number")){
            sql = "SELECT t_student.id,t_student.stuCode,t_student.name,t_student.sex,t_student.phone,t_student.grade,t_student.did " +
                    "from t_student,t_dormitory WHERE t_student.did = t_dormitory.id and t_dormitory.number = ?";
        }
        else if(conditions.equals("buildingname")){
            sql = "SELECT t_student.id,t_student.stuCode,t_student.name,t_student.sex,t_student.phone,t_student.grade,t_student.did " +
                    "from t_student,t_dormitory,t_building WHERE t_student.did = t_dormitory.id AND t_dormitory.bid = t_building.id and t_building.name = ?";
        }
        return template.query(sql,new BeanPropertyRowMapper<>(Student.class),information+"");
    }

    /**
     * 清空学生信息中的寝室信息
     * @param id
     */
    @Override
    public void emptyDid(Integer id) {
        String sql = null;
        sql = "update t_student set did = NULL where t_student.did = ?";
        template.update(sql,id);
    }

}
